<?php
class ChiTieuThongKe extends AppModel{
	var $name = 'ChiTieuThongKe';
	
	public function getBaoCaoTK($from, $to, $tthc_id) {
		$sql = "select * from chi_tieu_thong_kes ct
				left join (
					select ct.id, sum(tk.so_lieu) as dauky from chi_tieu_thong_kes ct 
					left join thong_kes tk on ct.id = tk.chi_tieu_thong_ke_id 
					where tk.created <= DATE('".$from."')
					group by ct.id
				) dauky on ct.id = dauky.id
				left join (
					select ct.id, sum(tk.so_lieu) as trongky from chi_tieu_thong_kes ct 
					left join thong_kes tk on ct.id = tk.chi_tieu_thong_ke_id 
					where tk.created between DATE('".$from."') and DATE('".$to."') 
					group by ct.id
				) trongky on ct.id = trongky.id
				left join (
					select ct.id, sum(tk.so_lieu) as cuoiky from chi_tieu_thong_kes ct 
					left join thong_kes tk on ct.id = tk.chi_tieu_thong_ke_id 
					where tk.created > DATE('".$to."') 
					group by ct.id
				) cuoiky on ct.id = cuoiky.id";
			
			if(!empty($tthc_id)) {
				$sql .= " where ct.tthc_id = $tthc_id";
			}
		// debug($sql);
		return $this->query($sql);
	}
	
	public function getSolieuThongKeByKqhc($kqhcId,$tthc_id) {
		$db = $this->getDataSource();

		$sql = "select * from chi_tieu_thong_kes ct 
			left join (select * from thong_kes where ketquahanhchinh_id = :kqhcId) 
			tk on tk.chi_tieu_thong_ke_id = ct.id where ct.tthc_id = :tthc_id  group by ct.id";
			
			
		return $db->fetchAll($sql, array('kqhcId' => $kqhcId, 'tthc_id' => $tthc_id));
	}
}